﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;
using SIPO.Library.DomainObject.Administration.Catalog;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recBillingRepo : IRepository<recBilling, string>
    {


        public int GetMaxID(string transtypeId)
        {
            int result = 1;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    if (transtypeId.Length == 1)
                    {
                        cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT MAX(CONVERT(INT,SUBSTRING(BillingNo,9,5))) BillingNo FROM transactions.recBilling where LEN(BillingNo)=13 and SUBSTRING(BillingNo,3,2) = MONTH(GETDATE()) AND SUBSTRING(BillingNo,1,1)=@TransTypeId)
	                                    set @max = (SELECT MAX(CONVERT(INT,SUBSTRING(BillingNo,9,5)))+1 recBilling FROM transactions.recBilling where LEN(BillingNo)=13 and SUBSTRING(BillingNo,3,2) = MONTH(GETDATE()) AND SUBSTRING(BillingNo,1,1)=@TransTypeId);
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end ";
                    }
                    else
                    {
                        cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT MAX(CONVERT(INT,SUBSTRING(BillingNo,10,5))) BillingNo FROM transactions.recBilling where LEN(BillingNo)=14 and SUBSTRING(BillingNo,4,2) = MONTH(GETDATE()) AND SUBSTRING(BillingNo,1,2)=@TransTypeId)
	                                    set @max = (SELECT MAX(CONVERT(INT,SUBSTRING(BillingNo,10,5)))+1 recBilling FROM transactions.recBilling where LEN(BillingNo)=14 and SUBSTRING(BillingNo,4,2) = MONTH(GETDATE()) AND SUBSTRING(BillingNo,1,2)=@TransTypeId);
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end ";
                    }
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@TransTypeId", SqlDbType.VarChar).Value = transtypeId;
                    try
                    {
                        conn.Open();
                        var objId = cmd.ExecuteScalar();
                        result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }


        public string GenerateBillingNoSP(string kode)
        {
            string result = string.Empty;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    
                    try
                    {
                        
                        conn.Open();
                        cmd.CommandText = "[dbo].[SP_GenTransCode]";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@kode", SqlDbType.VarChar).Value = kode;
                        SqlParameter prmResult = new SqlParameter();
                        prmResult.ParameterName = "@hasil";
                        prmResult.Direction = ParameterDirection.Output;
                        prmResult.SqlDbType = SqlDbType.VarChar;
                        prmResult.Size = 100;
                        cmd.Parameters.Add(prmResult);

                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                        var BillingNo = prmResult.Value;
                        result = BillingNo.ToString().Trim();
                        
                        //var objId = cmd.ExecuteScalar();
                        //result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }

        public List<recBilling> GetListInvoice(int pageSize, int currentPage, string sortName, string sortOrder, string queryRegistrasi,string queryAksi,string queryInvoice,string parameters)
        {
            List<recBilling> tt = new List<recBilling>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);

                    sbQuery.AppendFormat(@"
recBilling.BillingNo
,recBilling.DebtorSectorID
, recBilling.TransTypeID
, recBilling.TariffID
, recBilling.Value
, recBilling.InvoiceNo
, recBilling.InvoiceDetailID
, recBilling.Balance
, recBilling.PrevBillingNo
, recBilling.BillingDate
, recBilling.DueDate
, recBilling.IsCancel
, recBilling.ForPayment
, recBilling.SaikoNo
, recBilling.CreatedDate
, recBilling.CreatedBy
, recBilling.UpdatedDate
, recBilling.UpdatedBy
, recBilling.IsManual
, recBilling.TransTypeName
, recBilling.SectorName
, recBilling.SectorCode
, recBilling.SubSectorName
, recBilling.SubSectorCode
, recBilling.SubSubSectorName
, recBilling.SubSubSectorCode
, recBilling.SubSubSectorID
,recBilling.DebtorName
,recBilling.DebtorID 
,recBilling.UserID
,ISNULL(recBilling.IsHold,0) IsHold
,ISNULL(recBilling.IsRestructur,0) IsRestructur 
,recBilling.TransTypeCode
FROM
(");
sbQuery.AppendFormat(queryRegistrasi);
sbQuery.AppendFormat(" UNION ");
sbQuery.AppendFormat(queryInvoice);
if (queryAksi.Trim().Length > 1)
{
    sbQuery.AppendFormat(" UNION ");
    sbQuery.AppendFormat(queryAksi);
}
sbQuery.AppendFormat(" ) recBilling {0}",parameters);
                    
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.TariffID].ToString());
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recBilling.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.UpdatedBy].ToString();
                                t.IsManual = dr[recBilling.Fields.IsManual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsManual].ToString());
                                t.IsHold = dr[recBilling.Fields.IsHold] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsHold].ToString());
                                t.IsRestructur = dr[recBilling.Fields.IsRestructur] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsRestructur].ToString());
                               
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                t.recTransactionType.TransTypeCode = dr[recTransactionType.Fields.TransTypeCode] == DBNull.Value ? (short)0 : short.Parse(dr[recTransactionType.Fields.TransTypeCode].ToString());
                                t.recTransactionType.TransTypeID = dr[recTransactionType.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recTransactionType.Fields.TransTypeID].ToString());
                                t.recDebtorSector = new recDebtorSector(true);
                                t.recDebtorSector.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recDebtorSector.recSector.SectorCode = dr[recSector.Fields.SectorCode] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorCode].ToString();
                                t.recDebtorSector.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recDebtorSector.recSubSector.SubSectorCode = dr[recSubSector.Fields.SubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorCode].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorCode = dr[recSubSubSector.Fields.SubSubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorCode].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorID = dr[recSubSubSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSubSubSector.Fields.SubSubSectorID].ToString());
                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalarInvoice(SQL.Function.Aggregate function, string column, string queryRegistrasi, string queryInvoice, string queryAksi,string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        
                        case SQL.Function.Aggregate.Count:


sbQuery.AppendFormat(" SELECT COUNT({0}) FROM(",column);
sbQuery.AppendFormat(queryRegistrasi);
sbQuery.AppendFormat(" UNION ");
sbQuery.AppendFormat(queryInvoice);
if (queryAksi.Trim().Length > 1)
{
    sbQuery.AppendFormat(" UNION ");
    sbQuery.AppendFormat(queryAksi);
}
sbQuery.AppendFormat(" ) recBilling {0}",parameters);


                            break;
                        
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public object SelectScalarInfoInvoice(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat(@"SELECT COUNT({0}) FROM transactions.recBilling 
                                                 LEFT JOIN master.recTransactionType ON recBilling.TransTypeID= recTransactionType.TransTypeID
                                                 LEFT JOIN master.recDebtorSector ON recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                 LEFT JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID
                                                 LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID
                                                 LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID 
                                                 LEFT JOIN transactions.recInvoice ON recInvoice.InvoiceNo = recBilling.InvoiceNo
                                                 LEFT JOIN master.recDebtor ON recDebtor.DebtorID=recDebtorSector.DebtorID {1} ", column, parameters);
                            break;
                        
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recBilling> GetListInfoInvoice(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recBilling> tt = new List<recBilling>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recBilling.BillingNo
                                            , recBilling.DebtorSectorID
                                            , recBilling.TransTypeID
                                            , recBilling.TariffID
                                            , recBilling.Value
                                            , recBilling.InvoiceNo
                                            , recBilling.InvoiceDetailID
                                            , recBilling.Balance
                                            , recBilling.PrevBillingNo
                                            , recBilling.BillingDate
                                            , recBilling.DueDate
                                            , recBilling.IsCancel
                                            , recBilling.ForPayment
                                            , recBilling.SaikoNo
                                            , recBilling.CreatedDate
                                            , recBilling.CreatedBy
                                            , recBilling.UpdatedDate
                                            , recBilling.UpdatedBy
                                            , recBilling.IsManual
                                            , recTransactionType.TransTypeName
                                            , recSector.SectorName
                                            , recSector.SectorCode
                                            , recSubSector.SubSectorName
                                            , recSubSector.SubSectorCode
                                            , recSubSubSector.SubSubSectorName
                                            , recSubSubSector.SubSubSectorCode
                                            , recSubSubSector.SubSubSectorID
                                            ,recDebtor.DebtorName");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recBilling 
                                                 LEFT JOIN master.recTransactionType ON recBilling.TransTypeID= recTransactionType.TransTypeID
                                                 LEFT JOIN master.recDebtorSector ON recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                 LEFT JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID
                                                 LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID
                                                 LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
LEFT JOIN transactions.recInvoice ON recInvoice.InvoiceNo = recBilling.InvoiceNo
                                                 LEFT JOIN master.recDebtor ON recDebtor.DebtorID=recDebtorSector.DebtorID");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.TariffID].ToString());
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recBilling.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.UpdatedBy].ToString();
                                t.IsManual = dr[recBilling.Fields.IsManual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsManual].ToString());

                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                t.recDebtorSector = new recDebtorSector(true);
                                t.recDebtorSector.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recDebtorSector.recSector.SectorCode = dr[recSector.Fields.SectorCode] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorCode].ToString();
                                t.recDebtorSector.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recDebtorSector.recSubSector.SubSectorCode = dr[recSubSector.Fields.SubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorCode].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorCode = dr[recSubSubSector.Fields.SubSubSectorCode] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorCode].ToString();
                                t.recDebtorSector.recSubSubSector.SubSubSectorID = dr[recSubSubSector.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSubSubSector.Fields.SubSubSectorID].ToString());
                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public recBilling GetByInvoiceNo(string key)
        {
            recBilling t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, Balance, PrevBillingNo, BillingDate, DueDate, IsCancel, ForPayment, SaikoNo, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy FROM transactions.recBilling WHERE InvoiceNo = @InvoiceNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
								t = new recBilling();
                                t.BillingNo = dr[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.TariffID].ToString());
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recBilling.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.UpdatedBy].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public object SelectScalarReport(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX(DISTINCT {0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                            sbQuery.AppendFormat("  inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN(DISTINCT {0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                            sbQuery.AppendFormat("  inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                            sbQuery.AppendFormat("  inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT(DISTINCT {0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                            sbQuery.AppendFormat("  inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM(DISTINCT {0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                            sbQuery.AppendFormat("  inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG(DISTINCT {0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                            sbQuery.AppendFormat("  inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID {0}", parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recBilling> GetListReport(int pageSize, int currentPage, string parameters)
        {
            List<recBilling> tt = new List<recBilling>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT distinct");
                    sbQuery.AppendFormat("      recBilling.DebtorSectorID,recDebtor.DebtorID,recDebtor.DebtorName,recBilling.TransTypeID,recTransactionType.TransTypeName,recBilling.Value, recBilling.Balance, recBilling.BillingDate, recBilling.DueDate ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recBilling ");
                    sbQuery.AppendFormat("      inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                    sbQuery.AppendFormat("      inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                    sbQuery.AppendFormat("      inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                    sbQuery.AppendFormat("      inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");

                    sbQuery.AppendFormat("SELECT * FROM result_set");

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recBilling.Fields.DebtorSectorID].ToString());
                                t.recDebtor.DebtorID = dr[recDebtor.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recDebtor.Fields.DebtorID].ToString());
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recBilling.Fields.TransTypeID].ToString());
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? (decimal)0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal)0 : decimal.Parse(dr[recBilling.Fields.Balance].ToString());
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recBilling> GetListReports(int pageSize, int currentPage, string sortName ,string sortOrder,string parameters)
        {
            List<recBilling> tt = new List<recBilling>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("     recBilling.BillingNo, master.recTransactionType.TransTypeName, recDebtor.DebtorName, recBilling.BillingDate");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recBilling ");
                    sbQuery.AppendFormat("      inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                    sbQuery.AppendFormat("      inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                    sbQuery.AppendFormat("      inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                    sbQuery.AppendFormat(" INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo");
                    sbQuery.AppendFormat(" INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo"); 
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");

                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);


                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.BillingNo].ToString();
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public object SelectScalarReports(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo"); 
                            sbQuery.AppendFormat(" inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID {0}", parameters);
                             break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo"); 
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo"); 
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recBilling", column);
                            sbQuery.AppendFormat("  inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                            sbQuery.AppendFormat("  inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo");
                            sbQuery.AppendFormat(" INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo");
                            sbQuery.AppendFormat("  inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID {0}", parameters);
                           break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public DataTable GetListReport(string parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT distinct");
                    sbQuery.AppendFormat("      recBilling.DebtorSectorID,recDebtor.DebtorID,recDebtor.DebtorName,recBilling.TransTypeID,recTransactionType.TransTypeName,recBilling.Value, recBilling.Balance, recBilling.BillingDate, recBilling.DueDate ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recBilling ");
                    sbQuery.AppendFormat("      inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID");
                    sbQuery.AppendFormat("      inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID");
                    sbQuery.AppendFormat("      inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID");
                    sbQuery.AppendFormat("      inner join master.recUserSector on recUserSector.DebtorSectorID = recDebtorSector.DebtorSectorID");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");

                    sbQuery.AppendFormat("SELECT * FROM result_set");
                    cmd.CommandText = sbQuery.ToString();
                    
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            return dt;
        }

        public DataTable GetPrintVoucher(string parameter)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat(@"   SELECT  master.recTransactionType.TransTypeName 'JenTrans' ,
		case master.recTransactionType.TransTypeName 
		when 'Sanksi' then (Select letterno from transactions.recSanction where BillingNo = transactions.recBilling.BillingNo) 
		when 'Keberatan' then (Select ObjectionNo from transactions.recBilling where transactions.recBilling.BillingNo= transactions.recBilling.BillingNo)
		else '' end 'LetterNo', 
		transactions.recBilling.BillingNo 'NoRef', 
		transactions.recPayment.TransDate 'TglTrans', 
		master.recDebtor.DebtorName 'DebtorName', 
        transactions.recBilling.ForPayment,
        transactions.recBilling.PaymentValue 'Nominal', 
        transactions.recInvoice.BeSpelled 'Terbilang', 
        transactions.recPayment.CreatedBy 'CreateBy',
        null 'ReviewBy',
        null 'AcceptBy'
FROM       
transactions.recBilling 
INNER JOIN master.recDebtorSector ON transactions.recBilling.DebtorSectorID=master.recDebtorSector.DebtorSectorID 
INNER JOIN master.recDebtor ON master.recDebtorSector.DebtorID = master.recDebtor.DebtorID AND master.recDebtorSector.DebtorID = master.recDebtor.DebtorID 
INNER JOIN master.recTransactionType ON transactions.recBilling.TransTypeID = master.recTransactionType.TransTypeID 
INNER JOIN transactions.recInvoice ON transactions.recBilling.InvoiceNo = transactions.recInvoice.InvoiceNo 
INNER JOIN transactions.recPayment ON transactions.recInvoice.InvoiceNo = transactions.recPayment.InvoiceNo " );
                    sbQuery.AppendFormat(" {0} ", parameter);
                    sbQuery.AppendFormat(" ) ");

                    sbQuery.AppendFormat("SELECT * FROM result_set");
                    cmd.CommandText = sbQuery.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                        dt.Columns.Add("NoVoucher", typeof(string));
                        using (SqlCommand cmd1 = new SqlCommand("dbo.SP_GenTransCode", conn))
                        {
                            cmd1.CommandType = CommandType.StoredProcedure;
                            cmd1.Parameters.Add("@kode", SqlDbType.VarChar,10).Value = 13;
      
                            cmd1.Parameters.Add(new SqlParameter("@hasil", SqlDbType.VarChar,100));
                            cmd1.Parameters["@hasil"].Direction = ParameterDirection.Output;

                          
                            cmd1.ExecuteNonQuery();  // *** since you don't need the returned data - just call ExecuteNonQuery
                            string ans = cmd1.Parameters["@hasil"].Value.ToString();
                            dt.Rows[0][11] = ans;
                        }
                       

                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            return dt;
        }
        public List<recBilling> GetListPostReport(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recBilling> tt = new List<recBilling>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);
                    string paramBill = string.Empty;
                    string paramPay = string.Empty;
                    string paramAdjus = string.Empty;
                    //if (parameters != "")
                    //{
                    //    paramBill = "where (transactions.recBilling.isposting = 0 or transactions.recBilling.isposting is null) and transactions.recBilling.BillingDate " + parameters;
                    //    paramAdjus = "where  (transactions.recAdjus.isposting = 0 or transactions.recAdjus.isposting is null) and transactions.recAdjus.Createddate " + parameters;
                    //    paramPay = "where (transactions.recPayment.isposting = 0 or transactions.recPayment.isposting is null) and transactions.recPayment.PaymentDate " + parameters;
                    //}
                    //else {
                        paramPay = "where (transactions.recPayment.isposting = 0 or transactions.recPayment.isposting is null)";
                        paramBill = "where  (transactions.recBilling.isposting = 0 or transactions.recBilling.isposting is null)";
                        paramAdjus = "where  (transactions.recAdjus.isposting = 0 or transactions.recAdjus.isposting is null)";
                        
                    //}
                        StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], Bilno,Debtor,Sector,status,value,Date ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"     from
                                                (select
                                                transactions.recBilling.BillingNo 'Bilno', master.recDebtor.DebtorName 'Debtor', master.recSector.SectorName 'Sector', 'Tagihan' as 'status', transactions.recBilling.Value 'Value', transactions.recBilling.BillingDate as 'Date'
                                                from transactions.recBilling 
                                                inner join master.recDebtorSector on transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID ");
                     sbQuery.AppendFormat(" {0} ", paramBill);
                      sbQuery.AppendFormat(@" union all
                                                select convert(varchar,transactions.recAdjus.AdjusId), NULL 'Debtor', NULL 'Sector', 'Penyesuaian ' + AdjustmentTypeName as 'status transaksi', transactions.recAdjus.AdjusValue,transactions.recAdjus.AdjusDate 
                                                from transactions.recAdjus "); 
                    sbQuery.AppendFormat(" {0} ", paramAdjus);
                    sbQuery.AppendFormat(@" union all
                                                select convert(varchar,transactions.recPayment.PaymentID), master.recDebtor.DebtorName 'Debtor', master.recSector.SectorName 'Sector', 'Pembayaran' as 'status transaksi', transactions.recPayment.PaymentValue,transactions.recPayment.PaymentDate
                                                from transactions.recPayment 
                                                inner join master.recDebtorSector on transactions.recPayment.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID
                                               ");
                    sbQuery.AppendFormat(" {0} ", paramPay);
                    sbQuery.AppendFormat(") x");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(")");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);


                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr[1] == DBNull.Value ? string.Empty : dr[1].ToString();
                                t.recDebtor.DebtorName = dr[2] == DBNull.Value ? string.Empty : dr[2].ToString();
                                t.ForPayment = dr[3] == DBNull.Value ? string.Empty : dr[3].ToString();
                                t.UpdatedBy = dr[4] == DBNull.Value ? string.Empty : dr[4].ToString();
                                t.PaymentValue = dr[5] == DBNull.Value ? (decimal?)null : Convert.ToDecimal(dr[5].ToString());
                                t.BillingDate = dr[6] == DBNull.Value ? (DateTime?) null : Convert.ToDateTime (dr[6].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public object SelectScalarPostReport(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            string paramBill = string.Empty;
            string paramPay = string.Empty;
            string paramAdjus = string.Empty;
            //if (parameters != "")
            //{
            //    paramBill = "where (transactions.recBilling.isposting = 0 or transactions.recBilling.isposting is null) and transactions.recBilling.BillingDate " + parameters;
            //    paramAdjus = "where  (transactions.recAdjus.isposting = 0 or transactions.recAdjus.isposting is null) and transactions.recAdjus.Createddate " + parameters;
            //    paramPay = "where (transactions.recPayment.isposting = 0 or transactions.recPayment.isposting is null) and transactions.recPayment.PaymentDate " + parameters;
            //}
            //else
            //{
                paramPay = "where (transactions.recPayment.isposting = 0 or transactions.recPayment.isposting is null)";
                paramBill = "where (transactions.recBilling.isposting = 0 or transactions.recBilling.isposting is null)";
                paramAdjus = "where (transactions.recAdjus.isposting = 0 or transactions.recAdjus.isposting is null)";

            //}
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                  
                   
                    
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                           sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     max(notrans)
                                                        from 
                                                 (
                                                 select transactions.recBilling.BillingNo 'notrans' ");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@" transactions.recBilling 
                                                    inner join master.recDebtorSector on transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID ");
                            sbQuery.AppendFormat(" {0} ", paramBill);
                            sbQuery.AppendFormat("    UNION ALL ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" convert(varchar,transactions.recPayment.PaymentNo) 'notrans'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment 
                                                    inner join master.recDebtorSector on transactions.recPayment.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID 
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat("    UNION ALL ");
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@" convert(varchar,transactions.recAdjus.AdjusId) 'notrans'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@" transactions.recAdjus  
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramAdjus);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Min:
                          sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"    min(notrans)
                                                        from 
                                                 (
                                                 select transactions.recBilling.BillingNo 'notrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling 
                                                    inner join master.recDebtorSector on transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID ");
                            sbQuery.AppendFormat(" {0} ", paramBill);
                            sbQuery.AppendFormat("    UNION ALL ");
                    
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recPayment.PaymentNo) 'notrans'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment 
                                                    inner join master.recDebtorSector on transactions.recPayment.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID 
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat("    UNION ALL ");
                      
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recAdjus.AdjusId) 'notrans'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"from transactions.recAdjus  
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramAdjus);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     distinct(notrans)
                                                        from 
                                                 (
                                                 select transactions.recBilling.BillingNo 'notrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling 
                                                    inner join master.recDebtorSector on transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID ");
                            sbQuery.AppendFormat(" {0} ", paramBill);
                            sbQuery.AppendFormat("    UNION ALL ");
                       
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recPayment.PaymentNo) 'notrans'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment 
                                                    inner join master.recDebtorSector on transactions.recPayment.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID 
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat("    UNION ALL ");
                   
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recAdjus.AdjusId) 'notrans'");
                     
                            sbQuery.AppendFormat(@"from transactions.recAdjus  
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramAdjus);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     COUNT(notrans)
                                                        from 
                                                 (
                                                 select transactions.recBilling.BillingNo 'notrans','Tagihan' as 'status'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling 
                                                    inner join master.recDebtorSector on transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID ");
                            sbQuery.AppendFormat(" {0} ", paramBill);
                            sbQuery.AppendFormat("    UNION ALL ");

                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recPayment.PaymentNo) 'notrans','Pembayaran'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment 
                                                    inner join master.recDebtorSector on transactions.recPayment.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID 
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat("    UNION ALL ");
                           
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recAdjus.AdjusId) 'notrans','Penyesuaian'");
                           
                            sbQuery.AppendFormat(@"from transactions.recAdjus  
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramAdjus);
                            sbQuery.AppendFormat(" ) x");
                            sbQuery.AppendFormat("  {0} ", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"     sum(notrans)
                                                        from 
                                                 (
                                                 select transactions.recBilling.BillingNo 'notrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling 
                                                    inner join master.recDebtorSector on transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID ");
                            sbQuery.AppendFormat(" {0} ", paramBill);
                            sbQuery.AppendFormat("    UNION ALL ");
                          
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recPayment.PaymentNo) 'notrans'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment 
                                                    inner join master.recDebtorSector on transactions.recPayment.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID 
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat("    UNION ALL ");
                           
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recAdjus.AdjusId) 'notrans'");
                       
                            sbQuery.AppendFormat(@"from transactions.recAdjus  
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramAdjus);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        case SQL.Function.Aggregate.Avg:
                          sbQuery.AppendFormat("    SELECT ");
                            sbQuery.AppendFormat(@"    avg(notrans)
                                                        from 
                                                 (
                                                 select transactions.recBilling.BillingNo 'notrans'");
                            sbQuery.AppendFormat("    FROM ");
                            sbQuery.AppendFormat(@"transactions.recBilling 
                                                    inner join master.recDebtorSector on transactions.recBilling.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID ");
                            sbQuery.AppendFormat(" {0} ", paramBill);
                            sbQuery.AppendFormat("    UNION ALL ");
                          
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recPayment.PaymentNo) 'notrans'");
                            sbQuery.AppendFormat("  FROM ");
                            sbQuery.AppendFormat(@"transactions.recPayment 
                                                    inner join master.recDebtorSector on transactions.recPayment.DebtorSectorID = master.recDebtorSector.DebtorSectorID
                                                    inner join master.recDebtor on master.recDebtorSector.DebtorID = master.recDebtor.DebtorID
                                                    inner join master.recSector on master.recDebtorSector.SectorID = master.recSector.SectorID 
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramPay);
                            sbQuery.AppendFormat("    UNION ALL ");
                         
                            sbQuery.AppendFormat(@" select convert(varchar,transactions.recAdjus.AdjusId) 'notrans'");
                         
                            sbQuery.AppendFormat(@"from transactions.recAdjus  
                                                ");
                            sbQuery.AppendFormat("  {0} ", paramAdjus);
                            sbQuery.AppendFormat(" ) x");
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }
        
        public void executeProc(string ID, string ans)
        {
           
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            using (SqlCommand cmd1 = new SqlCommand("dbo.SP_PostingSIPOJK", conn))
            {
               

                try
                {
                    
                    cmd1.CommandType = CommandType.StoredProcedure;
                    cmd1.Parameters.Add("@PNOTrans", SqlDbType.VarChar, 50).Value = ID;
                    cmd1.Parameters.Add("@PostingGroup", SqlDbType.VarChar, 50).Value = ans;
                    cmd1.Parameters.Add(new SqlParameter("@return", SqlDbType.VarChar, 100));
                    cmd1.Parameters["@return"].Direction = ParameterDirection.Output;

                    cmd1.ExecuteNonQuery();  // *** since you don't need the returned data - just call ExecuteNonQuery
                    string ans1 = cmd1.Parameters["@return"].Value.ToString();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
               
            }
        }
        public string executeProcSumPosting(string PostingGroup)
        {
            string ans1 = string.Empty;
            SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();

            using (SqlCommand cmd1 = new SqlCommand("dbo.SP_SumPostingSIPOJK", conn))
            {


                try
                {

                    cmd1.CommandType = CommandType.StoredProcedure;
                   // cmd1.Parameters.Add("@PNOTrans", SqlDbType.VarChar, 50).Value = ID;
                    cmd1.Parameters.Add("@PostingGroup", SqlDbType.VarChar, 50).Value = PostingGroup;
                 //   cmd1.Parameters.Add(new SqlParameter("@return", SqlDbType.VarChar, 100));
                   // cmd1.Parameters["@return"].Direction = ParameterDirection.Output;

                    cmd1.ExecuteNonQuery();  // *** since you don't need the returned data - just call ExecuteNonQuery
                  //  ans1 = cmd1.Parameters["@return"].Value.ToString();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return "ok";
        }

        public List<string> GetTerminDueDate()
        {
            StringBuilder result = new StringBuilder();
            List<string> listString = new List<string>();
            string CurrentTermin = DateTime.Now.ToString("ddMM");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"SELECT
A.Tgl
,A.Bulan
FROM
(
SELECT SUBSTRING(Value,1,2) Tgl,SUBSTRING(Value,3,2) Bulan FROM master.recParameter
WHERE LOWER(ParameterCode) LIKE '%iuran1%'
OR LOWER(ParameterCode) LIKE '%iuran2%'
OR LOWER(ParameterCode) LIKE '%iuran3%'
OR LOWER(ParameterCode) LIKE '%iuran4%'
) A";

                        //cmd.Parameters.Add("@CurrentTime", SqlDbType.VarChar).Value = CurrentTermin;
                        cmd.CommandType = CommandType.Text;
                        conn.Open();

                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                listString.Add(dr["Tgl"].ToString()+"/"+dr["Bulan"].ToString());//+"/"+DateTime.Now.ToString("yyyy")); nanti kembalika remark nya

                            }
                        }



                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }


            return listString;
        }


        public List<string> GetTerminDueDateIsFormula()
        {
            StringBuilder result = new StringBuilder();
            List<string> listString = new List<string>();
            string CurrentTermin = DateTime.Now.ToString("ddMM");

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    try
                    {
                        cmd.CommandText = @"
                                            SELECT SUBSTRING(Value,1,2) Tgl,SUBSTRING(Value,3,2) Bulan FROM master.recParameter
                                            WHERE LOWER(ParameterCode) LIKE '%berkalaisformula%'
                                            ";

                        cmd.CommandType = CommandType.Text;
                        conn.Open();

                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                listString.Add(dr["Tgl"].ToString() + "/" + dr["Bulan"].ToString());//+"/"+DateTime.Now.ToString("yyyy")); nanti kembalika remark nya

                            }
                        }



                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }


            return listString;
        }

        public bool GetYearsReportYearsDues(string billingNo)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"select TOP 1 ISNULL(recImposition.YearReport,0)  YearReport
                                        ,ISNULL(recCalculation.YearDues,0) YearDues
                                        from transactions.recBilling
                                        inner join transactions.recCalculation on recBilling.CalculationID = recCalculation.CalculationID
                                        inner join transactions.recImpositionDetail on recImpositionDetail.ImpositionDetailID = recImpositionDetail.ImpositionDetailID
                                        inner join transactions.recImposition on recImpositionDetail.ImpositionID = recImposition.ImpositionID
                                        where recBilling.BillingNo=@BillingNo";

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billingNo;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                if(Convert.ToInt32(dr["YearReport"])<(Convert.ToInt32("YearDues")-1))
                                {
                                    result = true;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }

        public List<recBilling> GetListByDebtorAdd(string debtorid)
        {
            List<recBilling> tt = new List<recBilling>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd1 = conn.CreateCommand();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT b.BillingNo, b.DebtorSectorID, b.TransTypeID, b.TariffID, b.Value, b.InvoiceNo, 
                                      b.InvoiceDetailID, b.Balance, b.PrevBillingNo, b.BillingDate, b.DueDate, b.IsCancel, b.ForPayment,
                                      b.SaikoNo, b.CreatedDate, b.CreatedBy, b.UpdatedDate, b.UpdatedBy, b.CalculationID, b.PaymentValue, 
                                      b.IsManual, b.NoHoldBilling, b.IsHold, b.IsObjectionStatus, b.IsRestructur, b.IsPosting, b.ObjectionNo ,d.TransTypeName
                                      FROM transactions.recBilling b
                                      inner join master.recdebtorsector a on a.debtorsectorid=b.debtorsectorid 
                                      inner join master.recdebtor c on a.debtorid =c.debtorid  inner join master.rectransactiontype d on b.transtypeid =d.transtypeid where b.InvoiceNo='" + debtorid + "' and b.isrestructur=0";
                    cmd.CommandType = CommandType.Text;
                    cmd1.CommandText = @"SELECT b.BillingNo, b.DebtorSectorID, b.TransTypeID, b.TariffID, b.Value, b.InvoiceNo, 
                                      b.InvoiceDetailID, b.Balance, b.PrevBillingNo, b.BillingDate, b.DueDate, b.IsCancel, b.ForPayment,
                                      b.SaikoNo, b.CreatedDate, b.CreatedBy, b.UpdatedDate, b.UpdatedBy, b.CalculationID, b.PaymentValue, 
                                      b.IsManual, b.NoHoldBilling, b.IsHold, b.IsObjectionStatus, b.IsRestructur, b.IsPosting, b.ObjectionNo ,d.TransTypeName
                                      FROM transactions.recBilling b
                                      inner join master.recdebtorsector a on a.debtorsectorid=b.debtorsectorid 
                                      inner join master.recdebtor c on a.debtorid =c.debtorid inner join master.rectransactiontype d on b.transtypeid =d.transtypeid where b.InvoiceNo='" + debtorid + "' and b.isrestructur=1 ";
                    cmd1.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.TariffID].ToString());
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.CreatedDate].ToString());
                                t.UpdatedDate = dr[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.UpdatedBy].ToString();
                                t.CalculationID = dr[recBilling.Fields.CalculationID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.CalculationID].ToString());
                                t.PaymentValue = dr[recBilling.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.PaymentValue].ToString());
                                t.IsManual = dr[recBilling.Fields.IsManual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsManual].ToString());
                                t.NoHoldBilling = dr[recBilling.Fields.NoHoldBilling] == DBNull.Value ? string.Empty : dr[recBilling.Fields.NoHoldBilling].ToString();
                                t.IsHold = dr[recBilling.Fields.IsHold] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsHold].ToString());
                                t.IsObjectionStatus = dr[recBilling.Fields.IsObjectionStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsObjectionStatus].ToString());
                                t.IsRestructur = dr[recBilling.Fields.IsRestructur] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsRestructur].ToString());
                                t.IsPosting = dr[recBilling.Fields.IsPosting] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsPosting].ToString());
                                t.ObjectionNo = dr[recBilling.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ObjectionNo].ToString();
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                               
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                        conn.Open();
                        using (SqlDataReader dr1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr1.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr1[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr1[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr1[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr1[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr1[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr1[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr1[recBilling.Fields.TariffID].ToString());
                                t.Value = dr1[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr1[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr1[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr1[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr1[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr1[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr1[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr1[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr1[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr1[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr1[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr1[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr1[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr1[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.CreatedDate].ToString());
                                t.CreatedBy = "dis";
                                t.UpdatedDate = dr1[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr1[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.UpdatedBy].ToString();
                                t.CalculationID = dr1[recBilling.Fields.CalculationID] == DBNull.Value ? (int?)null : int.Parse(dr1[recBilling.Fields.CalculationID].ToString());
                                t.PaymentValue = dr1[recBilling.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr1[recBilling.Fields.PaymentValue].ToString());
                                t.IsManual = dr1[recBilling.Fields.IsManual] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsManual].ToString());
                                t.NoHoldBilling = dr1[recBilling.Fields.NoHoldBilling] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.NoHoldBilling].ToString();
                                t.IsHold = dr1[recBilling.Fields.IsHold] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsHold].ToString());
                                t.IsObjectionStatus = dr1[recBilling.Fields.IsObjectionStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsObjectionStatus].ToString());
                                t.IsRestructur = dr1[recBilling.Fields.IsRestructur] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsRestructur].ToString());
                                t.IsPosting = dr1[recBilling.Fields.IsPosting] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsPosting].ToString());
                                t.ObjectionNo = dr1[recBilling.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.ObjectionNo].ToString();
                                t.recTransactionType.TransTypeName = dr1[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr1[recTransactionType.Fields.TransTypeName].ToString();
                                
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recBilling> GetListByDebtorEdit(string debtorid,string resID)
        {
            List<recBilling> tt = new List<recBilling>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd1 = conn.CreateCommand();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT b.BillingNo, b.DebtorSectorID, b.TransTypeID, b.TariffID, b.Value, b.InvoiceNo, 
                                      b.InvoiceDetailID, b.Balance, b.PrevBillingNo, b.BillingDate, b.DueDate, b.IsCancel, b.ForPayment,
                                      b.SaikoNo, b.CreatedDate, b.CreatedBy, b.UpdatedDate, b.UpdatedBy, b.CalculationID, b.PaymentValue, 
                                      b.IsManual, b.NoHoldBilling, b.IsHold, b.IsObjectionStatus, b.IsRestructur, b.IsPosting, b.ObjectionNo ,d.TransTypeName
                                      FROM transactions.recBilling b
                                      inner join master.recdebtorsector a on a.debtorsectorid=b.debtorsectorid 
                                      inner join master.recdebtor c on a.debtorid =c.debtorid inner join master.rectransactiontype d on b.transtypeid =d.transtypeid where c.debtorid ='" + debtorid + "' and b.isrestructur=0";
                    cmd.CommandType = CommandType.Text;
                    cmd1.CommandText = @"SELECT b.BillingNo, b.DebtorSectorID, b.TransTypeID, b.TariffID, b.Value, b.InvoiceNo, 
                                      b.InvoiceDetailID, b.Balance, b.PrevBillingNo, b.BillingDate, b.DueDate, b.IsCancel, b.ForPayment,
                                      b.SaikoNo, b.CreatedDate, b.CreatedBy, b.UpdatedDate, b.UpdatedBy, b.CalculationID, b.PaymentValue, 
                                      b.IsManual, b.NoHoldBilling, b.IsHold, b.IsObjectionStatus, b.IsRestructur, b.IsPosting, b.ObjectionNo ,d.TransTypeName
                                      FROM transactions.recBilling b
                                      inner join master.recdebtorsector a on a.debtorsectorid=b.debtorsectorid 
                                      inner join master.recdebtor c on a.debtorid =c.debtorid inner join master.rectransactiontype d on b.transtypeid =d.transtypeid where c.debtorid ='" + debtorid + "' and b.isrestructur=1 and b.billingno in( select transdemandno from transactions.recrestructurdetail where restructurid = '" + resID + "')";
                    cmd1.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.TariffID].ToString());
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recBilling.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.UpdatedBy].ToString();
                                t.CalculationID = dr[recBilling.Fields.CalculationID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.CalculationID].ToString());
                                t.PaymentValue = dr[recBilling.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.PaymentValue].ToString());
                                t.IsManual = dr[recBilling.Fields.IsManual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsManual].ToString());
                                t.NoHoldBilling = dr[recBilling.Fields.NoHoldBilling] == DBNull.Value ? string.Empty : dr[recBilling.Fields.NoHoldBilling].ToString();
                                t.IsHold = dr[recBilling.Fields.IsHold] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsHold].ToString());
                                t.IsObjectionStatus = dr[recBilling.Fields.IsObjectionStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsObjectionStatus].ToString());
                                t.IsRestructur = dr[recBilling.Fields.IsRestructur] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsRestructur].ToString());
                                t.IsPosting = dr[recBilling.Fields.IsPosting] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsPosting].ToString());
                                t.ObjectionNo = dr[recBilling.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ObjectionNo].ToString();
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                               
                                tt.Add(t);
                               
                                t.Dispose();
                            }
                        }
                        conn.Open();
                        using (SqlDataReader dr1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr1.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr1[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr1[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr1[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr1[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr1[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr1[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr1[recBilling.Fields.TariffID].ToString());
                                t.Value = dr1[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr1[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr1[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr1[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr1[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr1[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr1[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr1[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr1[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr1[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr1[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr1[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr1[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr1[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.CreatedDate].ToString());
                                t.CreatedBy = "centang"; 
                                t.UpdatedDate = dr1[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr1[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr1[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.UpdatedBy].ToString();
                                t.CalculationID = dr1[recBilling.Fields.CalculationID] == DBNull.Value ? (int?)null : int.Parse(dr1[recBilling.Fields.CalculationID].ToString());
                                t.PaymentValue = dr1[recBilling.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr1[recBilling.Fields.PaymentValue].ToString());
                                t.IsManual = dr1[recBilling.Fields.IsManual] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsManual].ToString());
                                t.NoHoldBilling = dr1[recBilling.Fields.NoHoldBilling] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.NoHoldBilling].ToString();
                                t.IsHold = dr1[recBilling.Fields.IsHold] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsHold].ToString());
                                t.IsObjectionStatus = dr1[recBilling.Fields.IsObjectionStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsObjectionStatus].ToString());
                                t.IsRestructur = dr1[recBilling.Fields.IsRestructur] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsRestructur].ToString());
                                t.IsPosting = dr1[recBilling.Fields.IsPosting] == DBNull.Value ? (bool?)null : bool.Parse(dr1[recBilling.Fields.IsPosting].ToString());
                                t.ObjectionNo = dr1[recBilling.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr1[recBilling.Fields.ObjectionNo].ToString();
                                t.recTransactionType.TransTypeName = dr1[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr1[recTransactionType.Fields.TransTypeName].ToString();
                               
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recBilling> GetListByDebtor(string debtorid)
        {
            List<recBilling> tt = new List<recBilling>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd1 = conn.CreateCommand();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"SELECT b.BillingNo, b.DebtorSectorID, b.TransTypeID, b.TariffID, b.Value, b.InvoiceNo, 
                                      b.InvoiceDetailID, b.Balance, b.PrevBillingNo, b.BillingDate, b.DueDate, b.IsCancel, b.ForPayment,
                                      b.SaikoNo, b.CreatedDate, b.CreatedBy, b.UpdatedDate, b.UpdatedBy, b.CalculationID, b.PaymentValue, 
                                      b.IsManual, b.NoHoldBilling, b.IsHold, b.IsObjectionStatus, b.IsRestructur, b.IsPosting, b.ObjectionNo d.TransTypeName 
                                      FROM transactions.recBilling b
                                      inner join master.recdebtorsector a on a.debtorsectorid=b.debtorsectorid 
                                      inner join master.recdebtor c on a.debtorid =c.debtorid inner join master.rectransactiontype d on b.transtypeid =d.transtypeid where c.debtorid ='" + debtorid + "'";
                    cmd.CommandType = CommandType.Text;
                 
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recBilling t = new recBilling(true);
                                t.BillingNo = dr[recBilling.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.BillingNo].ToString();
                                t.DebtorSectorID = dr[recBilling.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recBilling.Fields.DebtorSectorID].ToString());
                                t.TransTypeID = dr[recBilling.Fields.TransTypeID] == DBNull.Value ? (short)0 : short.Parse(dr[recBilling.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recBilling.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.TariffID].ToString());
                                t.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recBilling.Fields.Value].ToString());
                                t.InvoiceNo = dr[recBilling.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.InvoiceNo].ToString();
                                t.InvoiceDetailID = dr[recBilling.Fields.InvoiceDetailID] == DBNull.Value ? (short?)null : short.Parse(dr[recBilling.Fields.InvoiceDetailID].ToString());
                                t.Balance = dr[recBilling.Fields.Balance] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.Balance].ToString());
                                t.PrevBillingNo = dr[recBilling.Fields.PrevBillingNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.PrevBillingNo].ToString();
                                t.BillingDate = dr[recBilling.Fields.BillingDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.BillingDate].ToString());
                                t.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                t.IsCancel = dr[recBilling.Fields.IsCancel] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsCancel].ToString());
                                t.ForPayment = dr[recBilling.Fields.ForPayment] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ForPayment].ToString();
                                t.SaikoNo = dr[recBilling.Fields.SaikoNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.SaikoNo].ToString();
                                t.CreatedDate = dr[recBilling.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.CreatedDate].ToString());
                                t.UpdatedDate = dr[recBilling.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recBilling.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recBilling.Fields.UpdatedBy].ToString();
                                t.CalculationID = dr[recBilling.Fields.CalculationID] == DBNull.Value ? (int?)null : int.Parse(dr[recBilling.Fields.CalculationID].ToString());
                                t.PaymentValue = dr[recBilling.Fields.PaymentValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recBilling.Fields.PaymentValue].ToString());
                                t.IsManual = dr[recBilling.Fields.IsManual] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsManual].ToString());
                                t.NoHoldBilling = dr[recBilling.Fields.NoHoldBilling] == DBNull.Value ? string.Empty : dr[recBilling.Fields.NoHoldBilling].ToString();
                                t.IsHold = dr[recBilling.Fields.IsHold] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsHold].ToString());
                                t.IsObjectionStatus = dr[recBilling.Fields.IsObjectionStatus] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsObjectionStatus].ToString());
                                t.IsRestructur = dr[recBilling.Fields.IsRestructur] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsRestructur].ToString());
                                t.IsPosting = dr[recBilling.Fields.IsPosting] == DBNull.Value ? (bool?)null : bool.Parse(dr[recBilling.Fields.IsPosting].ToString());
                                t.ObjectionNo = dr[recBilling.Fields.ObjectionNo] == DBNull.Value ? string.Empty : dr[recBilling.Fields.ObjectionNo].ToString();
                                t.recTransactionType.TransTypeName = dr[recTransactionType.Fields.TransTypeName] == DBNull.Value ? string.Empty : dr[recTransactionType.Fields.TransTypeName].ToString();
                               
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                       
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public DataTable GetListReportBalance(string parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(@"SELECT     sum(transactions.recBilling.Value) as 'Value', master.recSector.SectorName, master.recSubSector.SubSectorName
FROM         master.recSubSector INNER JOIN
                      master.recSector ON master.recSubSector.SectorID = master.recSector.SectorID INNER JOIN
                      master.recDebtorSector ON master.recSubSector.SubSectorID = master.recDebtorSector.SubSectorID INNER JOIN
                      transactions.recBilling ON master.recDebtorSector.DebtorSectorID = transactions.recBilling.DebtorSectorID "+ parameters +@"
                      and transactions.recBilling.Balance != 0 and transactions.recBilling.Balance is not null group by master.recSector.SectorName, master.recSubSector.SubSectorName ");
                   
                    cmd.CommandText = sbQuery.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            return dt;
        }
        public DataTable GetListReportUmurPiutang(string parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("select * from transactions.recRepAging");

                    cmd.CommandText = sbQuery.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            return dt;
        }
        public string GenReport(string kode, string kode2)
        {
            string result = string.Empty;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    try
                    {

                        conn.Open();
                        cmd.CommandText = "[dbo].[SP_GenrecRepUmurPiutang]";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@pPeriode1", SqlDbType.VarChar).Value = kode;
                        cmd.Parameters.Add("@pPeriode2", SqlDbType.VarChar).Value = kode2;
                        SqlParameter prmResult = new SqlParameter();
                        prmResult.ParameterName = "@Returns";
                        prmResult.Direction = ParameterDirection.Output;
                        prmResult.SqlDbType = SqlDbType.VarChar;
                        prmResult.Size = 100;
                        cmd.Parameters.Add(prmResult);

                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                        var BillingNo = prmResult.Value;
                        result = BillingNo.ToString().Trim();

                        //var objId = cmd.ExecuteScalar();
                        //result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }
        public string GenReportKertasKerja(string periode)
        {
            string result = string.Empty;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    try
                    {

                        conn.Open();
                        SqlCommand cmd1 = conn.CreateCommand();
                        cmd1.CommandText = "delete transactions.recrepworksheet";
                        cmd1.ExecuteNonQuery();
                        cmd.CommandText = "[dbo].[SP_GenRecRepKKPenerimaaOJK]";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@Periode", SqlDbType.VarChar).Value = periode;
                        //cmd.Parameters.Add("@pPeriode2", SqlDbType.VarChar).Value = kode2;
                        SqlParameter prmResult = new SqlParameter();
                        prmResult.ParameterName = "@returns";
                        prmResult.Direction = ParameterDirection.Output;
                        prmResult.SqlDbType = SqlDbType.VarChar;
                        prmResult.Size = 100;
                        cmd.Parameters.Add(prmResult);

                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                        var BillingNo = prmResult.Value;
                        result = BillingNo.ToString().Trim();

                        //var objId = cmd.ExecuteScalar();
                        //result = objId == DBNull.Value ? 1 : int.Parse(objId.ToString());
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return result;
        }
        public DataTable GetListReporKertasKerja(string parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("select * from transactions.recRepWorkSheet");

                    cmd.CommandText = sbQuery.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }

            return dt;
        }
    }
}
